package com.ningxia.water.mapper;

import com.ningxia.water.entity.ZtAreaInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.ApplicantVo;
import com.ningxia.water.vo.out.ReportVo;
import com.ningxia.water.vo.out.ZonePidVo;
import com.ningxia.water.vo.out.ZoneVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author hz
 * @since 2021-05-14
 */
public interface ZtAreaInfoMapper extends BaseMapper<ZtAreaInfo> {

    /**
     * 获取指定工业园区ID的具体地址
     * @param zoneId
     * @return
     */
    @Select("select address from zt_area_info where area_id = #{zoneId}")
    String getWellAddress(Integer zoneId);

    /**
     * 在工业园区模糊查询地址
     * @param address
     * @return
     */
    @Select("select address from zt_area_info " +
            "where area_id = #{zoneId} and address like CONCAT('%',#{address},'%')")
    String findWellAddressByLike(String address,Integer zoneId);

    /**
     * 查看此市下是否有工业园区
     * @param cityId
     * @return
     */
    @Select("SELECT count(1) as num FROM zt_area_info d WHERE d.city_id = #{cityId}")
    Integer findByCityId(Integer cityId);

    /**
     * 获取该工业园区下的所有用户
     * @param areaId
     * @return
     */
    @Select("SELECT\n" +
            "\tai.applicant_id,\n" +
            "\tai.applicant_name,\n" +
            "\ta.area_name as zoneName,\n" +
            "\tai.zone_id,\n" +
            "\tai.zone_type \n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN zt_area_info a ON ai.zone_id = a.area_id\n" +
            "WHERE\n" +
            "\tzone_type = 1 and ai.zone_id = #{areaId}")
    List<ApplicantVo> getAllApplicants(Integer areaId);


    /**
     * 获取工业园区年报
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "ai.area_id as id, " +
            "ai.area_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_area_info ai\n" +
            "LEFT JOIN ts_well_info wi ON wi.zone_id = ai.area_id and wi.zone_type = 1\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time BETWEEN concat(#{currentTime}, '-01') and concat(#{currentTime}, '-12')\n" +
            "GROUP BY ai.area_id\n")
    List<ReportVo> getAreaReportByYear(String currentTime);

    /**
     * 获取工业园区月报
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "ai.area_id as id, " +
            "ai.area_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_area_info ai\n" +
            "LEFT JOIN ts_well_info wi ON wi.zone_id = ai.area_id and wi.zone_type = 1\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time = #{currentTime}\n" +
            "GROUP BY ai.area_id")
    List<ReportVo> getAreaReportByMonth(String currentTime);

    /**
     * 获取工业园区日报
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "ai.area_id as id, " +
            "ai.area_name as name, " +
            "sum(dd.day_data)/10000 as volume\n" +
            "FROM zt_area_info ai\n" +
            "LEFT JOIN ts_well_info wi ON wi.zone_id = ai.area_id and wi.zone_type = 1\n" +
            "LEFT JOIN ts_day_data dd ON wi.well_id = dd.well_id and dd.time = #{currentTime}\n" +
            "GROUP BY ai.area_id")
    List<ReportVo> getAreaReportByDay(String currentTime);

    /**
     * 获取工业园区基本信息
     * @return
     */
    @Select("select " +
            "ci.city_id as pid, " +
            "ai.area_id as zoneId, " +
            "ai.area_name as area, " +
            "ai.address as address, " +
            "ci.city_name as city " +
            "from zt_area_info ai " +
            "left join zt_city_info ci " +
            "on ai.city_id = ci.city_id")
    List<ZoneVo> getAreaInfos();

    /**
     * 获取工业园区及其对应的城市id
     * @return
     */
    @Select("SELECT\n" +
            "  ci.city_id as pid,\n" +
            "\tai.area_id AS id,\n" +
            "\tai.area_name AS name\n" +
            "FROM\n" +
            "\tzt_area_info ai\n" +
            "\tLEFT JOIN zt_city_info ci ON ai.city_id = ci.city_id")
    List<ZonePidVo> getAreaRelationship();
}
